#!/usr/bin/perl
# 
# Read the departments data
# Insert data to sqlite database
# Author Hui Liu
use strict;
use warnings;
use DBI;

my $mapFile = $ARGV[0]? $ARGV[0]:"departments.txt";
my $dbh = DBI->connect("dbi:SQLite:../assets/tourMap.db","","",{RaiseError => 1, sqlite_unicode => 1, AutoCommit => 1});

open my $fh, $mapFile or die "Can't open file $mapFile";

my $bname;
my $lat;
my $lng;
my $ltype = 'B';

# clear table

$dbh->do("DROP TABLE IF EXISTS departments;");
$dbh->do("CREATE TABLE departments (_id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , name TEXT, details TEXT, locationID INTEGER, link TEXT, FOREIGN KEY(locationID) REFERENCES locations(_id));");

my $locIndex;

while ( my $line = <$fh>) {
    chomp $line;
    
    if ($line =~ /^<polygon name=\"(.*)\"/) {
#        $line =~ /^<polygon name=\"(.*)\"/;
#        $bname = $1;
        # find building id
        my $route = $dbh->prepare('SELECT _id FROM locations WHERE name = ?')
                or die "Couldn't prepare statement: " . $dbh->errstr;
        $route->execute($1) or die "Couldn't execute statement: " . $route->errstr;
        my @row = $route->fetchrow_array();
        $locIndex = $row[0];
       
    }elsif ($line =~ /^[A-Z]+/){
     #   print "INSERT INTO departments VALUES (NULL, \'$line\', NULL, $locIndex, NULL)";
        $dbh->do("INSERT INTO departments VALUES (NULL, \"$line\", NULL, $locIndex, NULL)") or die "Couldn't excute statement: " . $dbh->errstr;
    }
}



exit;
